﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Entity;
using System.Data.SqlClient;
using System.Collections.ObjectModel;
using Helper;

namespace DAL
{
    public class ProductDAL
    {
        //------------------ProductSearchById---------------------------------
        public List<objProduct> ProductSearchById(int id)
        {
            List<objProduct> lst = new List<objProduct>();

            objProduct obj = null;

            Collection<SqlParameter> parameterList = new Collection<SqlParameter>();

            parameterList.Add(new SqlParameter("@id", id));

            SqlDataReader dr = SQLHelper.ExecuteReader("spProductSearchById", parameterList);

            while(dr.Read())
            {
                obj = new objProduct();
                obj.Id = int.Parse(dr["id"].ToString());
                obj.IdL1 = int.Parse(dr["id_l1"].ToString());
                obj.IdL2 = int.Parse(dr["id_l2"].ToString());
                obj.IdL3 = int.Parse(dr["id_l3"].ToString());
                obj.Position1 = int.Parse(dr["position1"].ToString());
                obj.Position2 = int.Parse(dr["position2"].ToString());
                obj.Position3 = int.Parse(dr["position3"].ToString());
                obj.NewproFlag = int.Parse(dr["newpro_flag"].ToString());
                obj.ProsalesFlag = int.Parse(dr["prosales_flag"].ToString());
                obj.ProsaleFlag = int.Parse(dr["prosale_flag"].ToString());
                obj.Name = dr["name"].ToString();
                obj.Warranty = dr["warranty"].ToString();
                obj.MakeIn = dr["make_in"].ToString();
                obj.Price = float.Parse(dr["price"].ToString());
                obj.Sale = int.Parse(dr["sale"].ToString());
                obj.InfPro = dr["inf_pro"].ToString();
                obj.IntPro = dr["int_pro"].ToString();
                obj.DelFlg = int.Parse(dr["del_flg"].ToString());

                lst.Add(obj);
            }
            dr.Close();
            return lst;
        }

        //------------------ProductSearchById_l1---------------------------------
        public List<objProduct> ProductSearchById_l1(int id_l1)
        {
            List<objProduct> lst = new List<objProduct>();

            objProduct obj = null;

            Collection<SqlParameter> parameterList = new Collection<SqlParameter>();

            parameterList.Add(new SqlParameter("@id_l1", id_l1));

            SqlDataReader dr = SQLHelper.ExecuteReader("spProductSearchById_l1", parameterList);

            while (dr.Read())
            {
                obj = new objProduct();
                obj.Id = int.Parse(dr["id"].ToString());
                obj.IdL1 = int.Parse(dr["id_l1"].ToString());
                obj.IdL2 = int.Parse(dr["id_l2"].ToString());
                obj.IdL3 = int.Parse(dr["id_l3"].ToString());
                obj.Position1 = int.Parse(dr["position1"].ToString());
                obj.Position2 = int.Parse(dr["position2"].ToString());
                obj.Position3 = int.Parse(dr["position3"].ToString());
                obj.NewproFlag = int.Parse(dr["newpro_flag"].ToString());
                obj.ProsalesFlag = int.Parse(dr["prosales_flag"].ToString());
                obj.ProsaleFlag = int.Parse(dr["prosale_flag"].ToString());
                obj.Name = dr["name"].ToString();
                obj.Warranty = dr["warranty"].ToString();
                obj.MakeIn = dr["make_in"].ToString();
                obj.Price = float.Parse(dr["price"].ToString());
                obj.Sale = int.Parse(dr["sale"].ToString());
                obj.InfPro = dr["inf_pro"].ToString();
                obj.IntPro = dr["int_pro"].ToString();
                obj.DelFlg = int.Parse(dr["del_flg"].ToString());

                lst.Add(obj);
            }
            dr.Close();
            return lst;
        }

        //------------------ProductSearchById_l2---------------------------------
        public List<objProduct> ProductSearchById_l2(int id_l2)
        {
            List<objProduct> lst = new List<objProduct>();

            objProduct obj = null;

            Collection<SqlParameter> parameterList = new Collection<SqlParameter>();

            parameterList.Add(new SqlParameter("@id_l2", id_l2));

            SqlDataReader dr = SQLHelper.ExecuteReader("spProductSearchById_l2", parameterList);

            while (dr.Read())
            {
                obj = new objProduct();
                obj.Id = int.Parse(dr["id"].ToString());
                obj.IdL1 = int.Parse(dr["id_l1"].ToString());
                obj.IdL2 = int.Parse(dr["id_l2"].ToString());
                obj.IdL3 = int.Parse(dr["id_l3"].ToString());
                obj.Position1 = int.Parse(dr["position1"].ToString());
                obj.Position2 = int.Parse(dr["position2"].ToString());
                obj.Position3 = int.Parse(dr["position3"].ToString());
                obj.NewproFlag = int.Parse(dr["newpro_flag"].ToString());
                obj.ProsalesFlag = int.Parse(dr["prosales_flag"].ToString());
                obj.ProsaleFlag = int.Parse(dr["prosale_flag"].ToString());
                obj.Name = dr["name"].ToString();
                obj.Warranty = dr["warranty"].ToString();
                obj.MakeIn = dr["make_in"].ToString();
                obj.Price = float.Parse(dr["price"].ToString());
                obj.Sale = int.Parse(dr["sale"].ToString());
                obj.InfPro = dr["inf_pro"].ToString();
                obj.IntPro = dr["int_pro"].ToString();
                obj.DelFlg = int.Parse(dr["del_flg"].ToString());

                lst.Add(obj);
            }
            dr.Close();
            return lst;
        }

        //------------------spProductSearchById_l3---------------------------------
        public List<objProduct> ProductSearchById_l3(int id_l3)
        {
            List<objProduct> lst = new List<objProduct>();

            objProduct obj = null;

            Collection<SqlParameter> parameterList = new Collection<SqlParameter>();

            parameterList.Add(new SqlParameter("@id_l3", id_l3));

            SqlDataReader dr = SQLHelper.ExecuteReader("spProductSearchById_l3", parameterList);

            while (dr.Read())
            {
                obj = new objProduct();
                obj.Id = int.Parse(dr["id"].ToString());
                obj.IdL1 = int.Parse(dr["id_l1"].ToString());
                obj.IdL2 = int.Parse(dr["id_l2"].ToString());
                obj.IdL3 = int.Parse(dr["id_l3"].ToString());
                obj.Position1 = int.Parse(dr["position1"].ToString());
                obj.Position2 = int.Parse(dr["position2"].ToString());
                obj.Position3 = int.Parse(dr["position3"].ToString());
                obj.NewproFlag = int.Parse(dr["newpro_flag"].ToString());
                obj.ProsalesFlag = int.Parse(dr["prosales_flag"].ToString());
                obj.ProsaleFlag = int.Parse(dr["prosale_flag"].ToString());
                obj.Name = dr["name"].ToString();
                obj.Warranty = dr["warranty"].ToString();
                obj.MakeIn = dr["make_in"].ToString();
                obj.Price = float.Parse(dr["price"].ToString());
                obj.Sale = int.Parse(dr["sale"].ToString());
                obj.InfPro = dr["inf_pro"].ToString();
                obj.IntPro = dr["int_pro"].ToString();
                obj.DelFlg = int.Parse(dr["del_flg"].ToString());

                lst.Add(obj);
            }
            dr.Close();
            return lst;
        }

        //------------------spProductInsert---------------------------------
        public int ProductInsert(int id_l1, int id_l2, int id_l3, 
                                int position1, int position2, int position3, int newpro_flag, int prosales_flag,
                                int prosale_flag, string name, string warranty, string make_in, float price,
                                int sale, string inf_pro, string int_pro)
        {
            Collection<SqlParameter> parameterList = new Collection<SqlParameter>();

            parameterList.Add(new SqlParameter("@id_l1", id_l1));
            parameterList.Add(new SqlParameter("@id_l2", id_l2));
            parameterList.Add(new SqlParameter("@id_l3", id_l3));
            parameterList.Add(new SqlParameter("@position1", position1));
            parameterList.Add(new SqlParameter("@position2", position2));
            parameterList.Add(new SqlParameter("@position3", position3));
            parameterList.Add(new SqlParameter("@newpro_flag", newpro_flag));
            parameterList.Add(new SqlParameter("@prosales_flag", prosales_flag));
            parameterList.Add(new SqlParameter("@prosale_flag", prosale_flag));
            parameterList.Add(new SqlParameter("@name", name));
            parameterList.Add(new SqlParameter("@warranty", warranty));
            parameterList.Add(new SqlParameter("@make_in", make_in));
            parameterList.Add(new SqlParameter("@price", price));
            parameterList.Add(new SqlParameter("@sale", sale));
            parameterList.Add(new SqlParameter("@inf_pro", inf_pro));
            parameterList.Add(new SqlParameter("@int_pro", int_pro));

            int i = SQLHelper.ExecuteNonQuery("spProductInsert", parameterList);

            return i;
        }

        //------------------ProductUpdate---------------------------------
        public int ProductUpdate(int id, int id_l1, int id_l2, int id_l3,
                                int position1, int position2, int position3, int newpro_flag, int prosales_flag,
                                int prosale_flag, string name, string warranty, string make_in, float price,
                                int sale, string inf_pro, string int_pro)
        {
            Collection<SqlParameter> parameterList = new Collection<SqlParameter>();

            parameterList.Add(new SqlParameter("@id", id));
            parameterList.Add(new SqlParameter("@id_l1", id_l1));
            parameterList.Add(new SqlParameter("@id_l2", id_l2));
            parameterList.Add(new SqlParameter("@id_l3", id_l3));
            parameterList.Add(new SqlParameter("@position1", position1));
            parameterList.Add(new SqlParameter("@position2", position2));
            parameterList.Add(new SqlParameter("@position3", position3));
            parameterList.Add(new SqlParameter("@newpro_flag", newpro_flag));
            parameterList.Add(new SqlParameter("@prosales_flag", prosales_flag));
            parameterList.Add(new SqlParameter("@prosale_flag", prosale_flag));
            parameterList.Add(new SqlParameter("@name", name));
            parameterList.Add(new SqlParameter("@warranty", warranty));
            parameterList.Add(new SqlParameter("@make_in", make_in));
            parameterList.Add(new SqlParameter("@price", price));
            parameterList.Add(new SqlParameter("@sale", sale));
            parameterList.Add(new SqlParameter("@inf_pro", inf_pro));
            parameterList.Add(new SqlParameter("@int_pro", int_pro));

            int i = SQLHelper.ExecuteNonQuery("spProductUpdate", parameterList);

            return i;
        }

        ////------------------ProductDelete---------------------------------
        public int ProductDelete(int id)
        {
            Collection<SqlParameter> parameterList = new Collection<SqlParameter>();

            parameterList.Add(new SqlParameter("@id", id));

            int i = SQLHelper.ExecuteNonQuery("spProductDelete", parameterList);

            return i;
        }
    }
}


